package dao;

import pojo.Flight;
import util.DBConnectionUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class FlightDao {
    public List<Flight> getAllFlights() {
        PreparedStatement pstmt = null;
        List<Flight> flights = new ArrayList<>();
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "select * from flight";
        try {
            pstmt = conn.prepareStatement(sql);
            ResultSet resultSet = pstmt.executeQuery();
            while(resultSet.next()){
                Flight flight = new Flight();
                flight = new Flight();
                flight.setId(resultSet.getInt("id"));
                flight.setFlyId(resultSet.getString("fly_id"));
                flight.setFlyDesc(resultSet.getString("fly_desc"));
                flight.setDestination(resultSet.getString("destination"));
                flight.setFlyTime(resultSet.getDate("fly_time"));
               flights.add(flight);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.close(pstmt, conn);
            return flights;
        }
    }

    public Flight selectById(int id){
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        Flight flight =  null;
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "select * from flight where id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            resultSet = pstmt.executeQuery();
            while(resultSet.next()){
                flight = new Flight();
                flight.setId(resultSet.getInt("id"));
                flight.setFlyId(resultSet.getString("fly_id"));
                flight.setFlyDesc(resultSet.getString("fly_desc"));
                flight.setDestination(resultSet.getString("destination"));
                flight.setFlyTime(resultSet.getDate("fly_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.close(pstmt, resultSet, conn);
            return flight;
        }
    }

    public List<Flight> selectByDestination(String destination){
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        List<Flight> flights =  new ArrayList<>();
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "select * from flight where destination=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, destination);
            resultSet = pstmt.executeQuery();
            while(resultSet.next()){
                Flight flight = new Flight();
                flight.setId(resultSet.getInt("id"));
                flight.setFlyId(resultSet.getString("fly_id"));
                flight.setFlyDesc(resultSet.getString("fly_desc"));
                flight.setDestination(resultSet.getString("destination"));
                flight.setFlyTime(resultSet.getDate("fly_time"));
                flights.add(flight);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.close(pstmt, resultSet, conn);
            return flights;
        }
    }

    public List<Flight> selectByFlyTime(String flyTime){
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        List<Flight> flights =  new ArrayList<>();
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "select * from flight where fly_time=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, flyTime);
            resultSet = pstmt.executeQuery();
            while(resultSet.next()){
                Flight flight = new Flight();
                flight.setId(resultSet.getInt("id"));
                flight.setFlyId(resultSet.getString("fly_id"));
                flight.setFlyDesc(resultSet.getString("fly_desc"));
                flight.setDestination(resultSet.getString("destination"));
                flight.setFlyTime(resultSet.getDate("fly_time"));
                flights.add(flight);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBConnectionUtil.close(pstmt, resultSet, conn);
            return flights;
        }
    }

    public void addFlight(Flight flight){

        // 声明用到的对象
        PreparedStatement pstmt = null;
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "INSERT INTO flight(id, fly_id, fly_desc, destination, fly_time) VALUES (null, ?, ?, ?, ?)";
        try {
            //声明对象
            pstmt = conn.prepareStatement(sql);
            //给占位符赋值
            pstmt.setString(1, flight.getFlyId());
            pstmt.setString(2, flight.getFlyDesc());
            pstmt.setString(3, flight.getDestination());

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 定义日期时间格式
            if(flight.getFlyTime()!=null) {
                String formattedDate = sdf.format(flight.getFlyTime());
                pstmt.setString(4, formattedDate);
            }else{
                String formattedDate = sdf.format(new Date());
                pstmt.setString(4, formattedDate);
            }
            //执行sql语句
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBConnectionUtil.close(pstmt, conn);
        }
    }

    public void updateFlight(Flight flight){

        // 声明用到的对象
        PreparedStatement pstmt = null;
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "update flight set fly_id = ?,fly_desc = ?, destination = ?,fly_time =? where id =?";
        try {
            //声明对象
            pstmt = conn.prepareStatement(sql);
            //给占位符赋值
            pstmt.setString(1, flight.getFlyId());
            pstmt.setString(2, flight.getFlyDesc());
            pstmt.setString(3, flight.getDestination());

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); // 定义日期时间格式
            if(flight.getFlyTime()!=null) {
                String formattedDate = sdf.format(flight.getFlyTime());
                pstmt.setString(4, formattedDate);
            }else{
                String formattedDate = sdf.format(new Date());
                pstmt.setString(4, formattedDate);
            }

            pstmt.setInt(5, flight.getId());
            //执行sql语句
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBConnectionUtil.close(pstmt, conn);
        }
    }

    public void deleteFlightById(int id){

        // 声明用到的对象
        PreparedStatement pstmt = null;
        // 创建数据库连接
        Connection conn = DBConnectionUtil.getConn();
        // 插入数据库信息
        String sql = "delete from flight where id = ?";
        try {
            //声明对象
            pstmt = conn.prepareStatement(sql);
            //给占位符赋值
            pstmt.setInt(1, id);
            //执行sql语句
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            DBConnectionUtil.close(pstmt, conn);
        }
    }
}
